package com.me.dao.db;

import com.me.Utils.db.IDbUtil;
import com.me.annotation.Ignore;
import com.me.annotation.Inject;
import com.me.service.Services;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

@SuppressWarnings("All")
public class DbSet<TEntity> {
	private String entityName;
	private Class<TEntity> entityClass;
	private Map<String, Object[]> sqlMap = new HashMap<>();

	private ArrayList<String> otherDb;
	private ArrayList<KeyValuePair> constrains;

	@Inject private IDbUtil dbUtil;

	public DbSet(Class<TEntity> entityClass) {
		this.entityClass = entityClass;
		entityName = entityClass.getSimpleName();
		otherDb = new ArrayList<>();
		constrains = new ArrayList<>();
	}

	public void add(TEntity entity) {
		//INSERT INTO `user` (`id`, `username`, `email`, `password`, `name`, `phone`, `address`, `isadmin`, `isvalidate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("INSERT INTO `%s` (", entityClass.getSimpleName()));
			Field[] fields = entityClass.getDeclaredFields();
			int len = fields.length;
			for (Field f : fields) {
				if (f.getAnnotation(Ignore.class) != null) {
					len--;
					continue;
				}
				sb.append(String.format("`%s`,", f.getName()));
			}
			sb.replace(sb.length() - 1, sb.length(), ") VALUES (");
			Object[] objects = new Object[len];
			for (int i = 0; i < len; i++) {
				sb.append("?,");
				objects[i] = entityClass.getMethod(
						String.format("get%s%s", fields[i].getName().substring(0, 1).toUpperCase(), fields[i].getName().substring(1))
				).invoke(entity);
			}
			sb.replace(sb.length() - 1, sb.length(), ");");

			addSql(sb.toString(), objects);
		} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
			e.printStackTrace();
		}
	}

	public void addRange(TEntity[] entitys) {
		for (TEntity entity : entitys) {
			add(entity);
		}
	}

	public void addRange(Collection<TEntity> entitys) {
		for (TEntity entity : entitys) {
			add(entity);
		}
	}

	public void remove(TEntity entity) {
		//delete FROM user where id=?
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("delete FROM `%s` where ", entityClass.getSimpleName()));

			Field[] fields = entityClass.getDeclaredFields();
			List<Object> objects = new ArrayList<>();
			for (int i = 0; i < fields.length; i++) {
				if (fields[i].getAnnotation(Ignore.class) != null) {
					continue;
				}
				sb.append(toKeyWord(fields[i].getName())).append("=?");
				sb.append(" and ");
				Object val = entityClass.getMethod(
						String.format("get%s%s", fields[i].getName().substring(0, 1).toUpperCase(), fields[i].getName().substring(1))
				).invoke(entity);
				objects.add(val);
			}
			sb.replace(sb.lastIndexOf("and"),sb.length(),"");
			sb.append(";");
			addSql(sb.toString(), objects.toArray());
		} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
			e.printStackTrace();
		}

	}

	public void removeRange(TEntity[] entitys) {
		for (TEntity entity : entitys) {
			remove(entity);
		}
	}

	public void removeRange(Collection<TEntity> entitys) {
		for (TEntity entity : entitys) {
			remove(entity);
		}
	}

	public void update(TEntity entity, String key, Object value) {
		//UPDATE user SET `headPath` = ?, `name` = ?, `sex` = ?, `admin` = ? WHERE (`id` = ?);
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("UPDATE `%s` SET ", entityClass.getSimpleName()));
			Field[] fields = entityClass.getDeclaredFields();

			List<Object> objects = new ArrayList<>();
			int len = fields.length;

			for (int i = 0; i < fields.length; i++) {
				if (fields[i].getAnnotation(Ignore.class) != null) continue;

				sb.append(toKeyWord(fields[i].getName())).append("=?,");

				Object val = entityClass.getMethod(
						String.format("get%s%s", fields[i].getName().substring(0, 1).toUpperCase(), fields[i].getName().substring(1))
				).invoke(entity);
				objects.add(val);
			}
			objects.add(value);
			sb.replace(sb.length() - 1, sb.length(), String.format(" WHERE `%s`=?;", key));
			addSql(sb.toString(), objects.toArray());
		} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
			e.printStackTrace();
		}
	}

	public void updateRange(TEntity[] entitys, String key, Object value) {
		for (TEntity entity : entitys) {
			update(entity, key, value);
		}
	}

	public void updateRange(Collection<TEntity> entitys, String key, Object value) {
		for (TEntity entity : entitys) {
			update(entity, key, value);
		}
	}

	public TEntity find(String key, String value) {
		return find(new KeyValuePairs().add(key, value));
	}

	public TEntity find(KeyValuePairs kvs) {
		try {
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("SELECT %s.* FROM %s where ",
					toKeyWord(entityClass.getSimpleName()),
					toKeyWord(entityClass.getSimpleName()) + getOtherDb()
			));
			Object[] objs = new Object[kvs.size()];
			KeyValuePair kv = kvs.poll();
			while (kv != null) {
				sb.append(toKeyWord(kv.getKey())).append("=?");
				objs[objs.length - kvs.size() - 1] = kv.getValue();
				if (kvs.peek() != null) {
					sb.append("  and  ");
				}
				kv = kvs.poll();
			}
			sb.append(getConstrains());
			sb.append(";");
			return runner.query(sb.toString(), new BeanHandler<TEntity>(entityClass), objs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Collection<TEntity> findLike(String key, String condition) {
		return findLike(key, condition, -1, -1);
	}

	public Collection<TEntity> findLike(String key, String condition, int page, int size) {
		try {
			StringBuilder sb = new StringBuilder();
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			String sql = String.format("SELECT %s.* FROM %s where `%s` like '%s' %s",
					toKeyWord(entityClass.getSimpleName()),
					toKeyWord(entityClass.getSimpleName()) + getOtherDb(),
					key,
					condition,
					page < 0 && size < 0 ? "" : String.format("limit %d,%d", page * size, size)
			);
			sb.append(getConstrains());
			sb.append(";");
			return runner.query(sql, new BeanListHandler<TEntity>(entityClass));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Collection<TEntity> findAll(String key, String value) {
		return findAll(new KeyValuePairs().add(key, value));
	}

	public Collection<TEntity> findAll(KeyValuePairs kvs) {
		try {
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("SELECT * FROM %s where ",
					toKeyWord(entityClass.getSimpleName()) + getOtherDb()
			));
			Object[] objs = new Object[kvs.size()];
			KeyValuePair kv = kvs.poll();
			while (kv != null) {
				sb.append(toKeyWord(kv.getKey())).append("=?");
				objs[objs.length - kvs.size() - 1] = kv.getValue();
				if (kvs.peek() != null) {
					sb.append("  and  ");
				}
				kv = kvs.poll();
			}
			sb.append(getConstrains());
			sb.append(";");
			return runner.query(sb.toString(), new BeanListHandler<TEntity>(entityClass), objs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Collection<TEntity> findAll(String key, String value, int page, int size) {
		return findAll(new KeyValuePairs().add(key, value), page, size);
	}

	public Collection<TEntity> findAll(KeyValuePairs kvs, int page, int size) {
		try {
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("SELECT %s.* FROM %s ",
					toKeyWord(entityClass.getSimpleName()),
					toKeyWord(entityClass.getSimpleName()) + getOtherDb()
			));
			Object[] objs = null;
			KeyValuePair kv = null;
			if (kvs != null) {
				sb.append("where ");
				objs = new Object[kvs.size()];
				kv = kvs.poll();
			}
			while (kv != null) {
				sb.append(toKeyWord(kv.getKey())).append("=?");
				objs[objs.length - kvs.size() - 1] = kv.getValue();
				if (kvs.peek() != null) {
					sb.append("  and  ");
				}
				kv = kvs.poll();
			}
			sb.append(getConstrains());
			sb.append(String.format(" limit %d,%d;", page * size, size));
			return runner.query(sb.toString(), new BeanListHandler<TEntity>(entityClass), objs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private String toKeyWord(String name) {
		name = "`" + name;
		if (name.contains(".")) {
			name = name.replace(".", "`.");
		} else {
			name = name + "`";
		}

		return name;
	}

	public Collection<TEntity> getCollection() {
		return getRange(-1, -1);
	}

	public Collection<TEntity> getRange(int page, int size) {
		try {
			StringBuilder sb = new StringBuilder();
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			String sql = String.format("SELECT %s.* FROM %s %s",
					toKeyWord(entityClass.getSimpleName()),
					toKeyWord(entityClass.getSimpleName()) + getOtherDb(),
					page < 0 && size < 0 ? "" : String.format("limit %d,%d", page * size, size)
			);
			sb.append(getConstrains());
			sb.append(";");
			return runner.query(sql, new BeanListHandler<TEntity>(entityClass));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public boolean existAll(String key,Object value) {
		return existAll(new KeyValuePairs().add(key, value));
	}

	public boolean existAll(KeyValuePairs kvs) {
		try {
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("SELECT COUNT(*) FROM %s where ",
					toKeyWord(entityClass.getSimpleName()) + getOtherDb()
			));
			Object[] objs = new Object[kvs.size()];
			KeyValuePair kv = kvs.poll();
			while (kv != null) {
				sb.append(toKeyWord(kv.getKey())).append("=?");
				objs[objs.length - kvs.size() - 1] = kv.getValue();
				if (kvs.peek() != null) {
					sb.append("  and  ");
				}
				kv = kvs.poll();
			}
			sb.append(getConstrains());
			sb.append(";");
			if (runner.query(sb.toString(), new ScalarHandler<Long>(), objs) > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean existAny(String key, String value) {
		return existAny(new KeyValuePairs().add(key, value));
	}

	public boolean existAny(KeyValuePairs kvs) {
		try {
			QueryRunner runner = new QueryRunner(dbUtil.getDataSource());
			StringBuilder sb = new StringBuilder();
			sb.append(String.format("SELECT COUNT(*) FROM %s where ",
					toKeyWord(entityClass.getSimpleName()) + getOtherDb()
			));
			Object[] objs = new Object[kvs.size()];
			KeyValuePair kv = kvs.poll();
			while (kv != null) {
				sb.append("`").append(kv.getKey()).append("`=?");
				objs[objs.length - kvs.size() - 1] = kv.getValue();
				if (kvs.peek() != null) {
					sb.append("  or  ");
				}
				kv = kvs.poll();
			}
			sb.append(getConstrains());
			sb.append(";");
			if (runner.query(sb.toString(), new ScalarHandler<Long>(), objs) > 0) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public DbSet<TEntity> contact(DbSet other) {
		DbSet<TEntity> newDb = new DbSet<TEntity>(entityClass);
		Services.inject(newDb);
		newDb.otherDb = (ArrayList<String>) otherDb.clone();
		newDb.constrains = (ArrayList<KeyValuePair>) constrains.clone();
		newDb.otherDb.add(other.entityName);
		return newDb;
	}

	public DbSet<TEntity> addConstrain(String key, String value) {
		return addConstrain(new KeyValuePairs().add(key, value));
	}

	public DbSet<TEntity> addConstrain(KeyValuePairs kvs) {
		DbSet<TEntity> newDb = new DbSet<TEntity>(entityClass);
		Services.inject(newDb);
		newDb.otherDb = (ArrayList<String>) otherDb.clone();
		newDb.constrains = (ArrayList<KeyValuePair>) constrains.clone();
		while (kvs.peek() != null) {
			newDb.constrains.add(kvs.poll());
		}
		return newDb;
	}

	public String getEntityName() {
		return entityName;
	}

	public void save() {
		if (sqlMap.size() <= 0) {
			return;
		}
		Connection conn = null;
		try {
			conn = dbUtil.getDataSource().getConnection();
			conn.setAutoCommit(false);
			QueryRunner runner = new QueryRunner();

			ListIterator<Map.Entry<String,Object[]>> ite=new ArrayList<Map.Entry<String,Object[]>>(sqlMap.entrySet()).listIterator(sqlMap.size());
			while(ite.hasPrevious()) {
				Map.Entry<String, Object[]> entry=ite.previous();
				runner.update(conn, entry.getKey(), entry.getValue());
			}
			DbUtils.commitAndCloseQuietly(conn);
		} catch (SQLException e) {
			if (conn != null) {
				DbUtils.rollbackAndCloseQuietly(conn);
			}
			e.printStackTrace();
		}
	}

	private String getOtherDb() {
		StringBuilder sb = new StringBuilder();
		for (String s : otherDb) {
			sb.append(",`");
			sb.append(s);
			sb.append("`");
		}
		return sb.toString();
	}

	private String getConstrains() {
		StringBuilder sb = new StringBuilder();
		for (KeyValuePair kv : constrains) {
			sb.append(" and ").append(kv.getKey()).append("=").append(kv.getValue());
		}
		return sb.toString();
	}

	private void addSql(String sql, Object[] objects) {
		if (sqlMap == null) sqlMap = new LinkedHashMap<>();
		if (sqlMap.containsKey(sql)) return;
		sqlMap.put(sql, objects);
	}

}
